﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using database_constants;


/// <summary>
/// Summary description for DBQuery
/// </summary>
namespace database_entry
{
    public class DBQuery
    {
        SqlConnection conn = null;
        SqlDataReader reader = null;
        private SqlDataSource DATA_SOURCE;
        private string DB_NAME = "data";

        #region Constructor

        public DBQuery(SqlConnection connect, SqlDataSource source, string name)
        {
            try
            {
                // instantiate and open connection
                conn = connect;
                conn.Open();
            }
            catch (Exception e)
            { System.Console.WriteLine("SQL Database Connection Error:" + e); }
            DATA_SOURCE = source;
            DB_NAME = name;
        }
        public DBQuery(SqlConnection connect, string name)
        {
            try
            {
                // instantiate and open connection
                conn = connect;
                conn.Open();
            }
            catch (Exception e)
            { System.Console.WriteLine("SQL Database Connection Error:" + e); }
            DB_NAME = name;
        }
        public DBQuery(SqlDataSource source, string name)
        {
            DATA_SOURCE = source;
            DB_NAME = name;
        }


        public void closeConnection()
        {
            // close reader
                if (reader != null)
                {
                    reader.Close();
                }

                // close connection
                if (conn != null)
                {
                    conn.Close();
                }

        }

        #endregion

        #region INSERT
        public void InsertItem(DBEntry entry)
        {
            try
            {
                SqlCommand cmd = entry.InsertItem();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            { System.Console.WriteLine("Error Inserting into Database: "+e); }
        }
        #endregion

        #region REMOVE
        public void DeleteClientEntries(int ClientID)
        {
            try
            {
                SqlCommand cmd = new SqlCommand("DELETE FROM " + DB_NAME + " WHERE [ClientID] = @ClientID");
                SqlParameter param = new SqlParameter("@ClientID", ClientID);
                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { System.Console.WriteLine("Error Occured in SQL Database Access: "+e); }
        }
        public void DeleteEntry(int ScheduleID)
        {
            try
            {
                SqlCommand cmd = new SqlCommand("DELETE FROM " + DB_NAME + " WHERE [ScheduleID] = @ScheduleID");
                SqlParameter param = new SqlParameter("@ScheduleID", ScheduleID);
                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { System.Console.WriteLine("Error Occured in SQL Database Access: "+e); }
        }
        public void DeleteEntriesBeforeDate(SqlDateTime dt)
        {
            try
            {
                SqlCommand cmd = new SqlCommand("DELETE FROM " + DB_NAME + " WHERE [EndDate] < @EndDate");
                SqlParameter param = new SqlParameter("@EndDate", dt);
                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e) { System.Console.WriteLine("Error Occured in SQL Database Access: "+ e); }
        }
        #endregion

        #region SELECT

        public void SelectClientEntries(int client)
        {
            DATA_SOURCE.SelectCommand = "SELECT * FROM " + DB_NAME + " WHERE [ClientID] = '" + client.ToString() + "'";
        }
        public void SelectEntriesBetweenDates(int client, SqlDateTime start, SqlDateTime finish)
        {
            DATA_SOURCE.SelectCommand = "SELECT * FROM " + DB_NAME + " WHERE [ClientID] = "+client+
                " AND [StartDate] >= " + start + " AND [EndDate] <= " + finish;
        }
        public List<DBEntry> GetEntries(SqlCommand command)
        {
            List<DBEntry> newList = new List<DBEntry>();
            SqlConnection conn = new SqlConnection(Constants.DB_CONNECTION);
            try
            {
                conn.Open();
            }
            catch (Exception ex) { System.Console.WriteLine("Error connecting to SQL Database: " + ex.ToString()); }
            command.Connection = conn;
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                newList.Add(new DBEntry(reader));
            }
            return newList;
        }
        public string getSelectDateString(int client, SqlDateTime today)
        {
            string query = "SELECT [ClientID],[StartTime],[StartDate],[EndTime],[EndDate],[Notes],[Daily],[Weekly],[Monthly] FROM " + DB_NAME +
                    " WHERE ([ClientID] = "+client+" AND (([StartDate] = '"+today+"') OR ([StartDate] < '"+today+"' AND [EndDate] > '"+today+"'  AND [Weekly] = 1 AND ";
                DateTime dt = (DateTime)today;
                switch (dt.DayOfWeek)
                {
                    case DayOfWeek.Monday:
                        query += "[Mon] = 1))) ORDER BY [StartTime]";
                        break;
                    case DayOfWeek.Tuesday:
                        query += "[Tue] = 1))) ORDER BY [StartTime]";
                        break;
                    case DayOfWeek.Wednesday:
                        query += "[Wed] = 1))) ORDER BY [StartTime]";
                        break;
                    case DayOfWeek.Thursday:
                        query += "[Thu] = 1))) ORDER BY [StartTime]";
                        break;
                    case DayOfWeek.Friday:
                        query += "[Fri] = 1))) ORDER BY [StartTime]";
                        break;
                    case DayOfWeek.Saturday:
                        query += "[Sat] = 1))) ORDER BY [StartTime]";
                        break;
                    case DayOfWeek.Sunday:
                        query += "[Sun] = 1))) ORDER BY [StartTime]";
                        break;
                };
            return query;
        }
        public void SelectEntriesOnDate(int client, SqlDateTime today)
        {
            DATA_SOURCE.SelectCommand = getSelectDateString(client,today);
        }
        public SqlCommand getSelectDateCommand(int client, SqlDateTime today)
        {
            string query = "SELECT * FROM data" + " WHERE (ClientID = @cID AND ((StartDate = @today) OR (StartDate < @today AND EndDate > @today  AND (Daily = 1 OR (Weekly = 1 AND ";
            DateTime dt = (DateTime)today;
            switch (dt.DayOfWeek)
            {
                case DayOfWeek.Monday:
                    query += "Mon = 1))))) ORDER BY StartTime";
                    break;
                case DayOfWeek.Tuesday:
                    query += "Tue = 1))))) ORDER BY StartTime";
                    break;
                case DayOfWeek.Wednesday:
                    query += "Wed = 1))))) ORDER BY StartTime";
                    break;
                case DayOfWeek.Thursday:
                    query += "Thu = 1))))) ORDER BY StartTime";
                    break;
                case DayOfWeek.Friday:
                    query += "Fri = 1))))) ORDER BY StartTime";
                    break;
                case DayOfWeek.Saturday:
                    query += "Sat = 1))))) ORDER BY StartTime";
                    break;
                case DayOfWeek.Sunday:
                    query += "Sun = 1))))) ORDER BY StartTime";
                    break;
            };
            SqlCommand comm = new SqlCommand(query);
            comm.Parameters.Add(new SqlParameter("@cID", client));
            comm.Parameters.Add(new SqlParameter("@today", today));
            return comm;
        }

        #endregion

        #region Arrays
        public List<DBEntry> GetArrayForDate(int client, SqlDateTime today)
        {
            List<DBEntry> ENTRIES = new List<DBEntry>();
            SqlCommand command = new SqlCommand(getSelectDateString(client, today));
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                ENTRIES.Add(new DBEntry(reader));
            }
            return ENTRIES;

        }
        /*public string GetReaderForDate(int client, SqlDateTime today)
        {
            string s = "NOTHING";
            SqlCommand comm = new SqlCommand("SELECT * FROM data");
            try
            {
                SqlDataReader reader = comm.ExecuteReader();       
                while (reader.Read())
                {
                    s += (String.Format("{1:t} - {3:t}, Client:{0} Note:{5}", reader[1], reader[3], reader[0], reader[5]) + "\n");
                }
            }
            catch (Exception e) { Console.WriteLine("READER EXCEPTION OCCURED :"+e.ToString()); }
            return s;
        }*/
        #endregion
    }
}
